package com.cloudwise.util;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author ：Teacher陈
 * @date ：Created in 2021/1/18 15:24
 * @description：所有dao的基础类
 * @modified By：
 * @version: 1
 */
public class BaseDao {

    static String password = "root";
    static String driver = "com.mysql.jdbc.Driver";
    static String username = "root";
    static String url = "jdbc:mysql://localhost:3306/cloudwise?useUnicode=true&characterEncoding=utf-8&useSSL=false";

    //1加载驱动
    static {
        try {
            Class.forName(driver);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    /**
     * @param
     * @return java.sql.Connection
     * @create by: Teacher陈
     * @description: //2获取连接
     * @create time: 2021/1/18 15:32
     */
    public static Connection getConnection() {
        try {
            Connection connection = DriverManager.getConnection(url, username, password);
            return connection;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * @create by: Teacher陈
     * @description:关闭连接
     * @create time: 2021/1/18 16:30
     * @param resultSet
     * @param preparedStatement
     * @param connection
     * @return void
     */

    public void closeAll(ResultSet resultSet,PreparedStatement preparedStatement, Connection connection) {

        //6关闭连接

        try {
            if(resultSet!=null){
                resultSet.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            if (preparedStatement != null) {

                preparedStatement.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            if (connection != null) {

                connection.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * @param sql,params
     * @return
     * @create by: Teacher陈
     * @description: 通用增加删除，修改方法
     * @create time: 2021/1/18 16:22
     */
    public boolean executeUpdate(String sql, Object...params) {
        //3拼写sql语句

        Connection connection = BaseDao.getConnection();
        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            //设置参数
            if(params!=null&&params.length>0){
                for (int i = 0; i < params.length; i++) {
                    preparedStatement.setObject((i+1),params[i]);
                }
            }
            //4执行sql语句
            int i = preparedStatement.executeUpdate();
            //5返回结果集
            if (i > 0) {
                System.out.println("操作成功");
                return true;
            } else {
                System.out.println("操作失败");
                return false;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            closeAll(null,preparedStatement,connection);
        }


        return false;
    }

    /**
     * @param sql,params
     * @return
     * @create by: Teacher陈
     * @description: 通用查询方法
     * @create time: 2021/1/18 16:22
     */
    public List<Map<String,Object>> executeQuery(String sql, Object...params) {
        //使用rowList封装所有行数据
        List<Map<String,Object>> rowList= new ArrayList<>();
        //3拼写sql语句
        Connection connection = BaseDao.getConnection();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet=null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            //设置参数
            if(params!=null&&params.length>0){
                for (int i = 0; i < params.length; i++) {
                    preparedStatement.setObject((i+1),params[i]);
                }
            }
            //4执行sql语句
             resultSet = preparedStatement.executeQuery();
            ResultSetMetaData metaData = resultSet.getMetaData();

            //5返回结果集,解析resultSet，
                //next()进入下一行，相当于sql中的游标
            while (resultSet.next()){
                //定义一个map集合，封装一行数据（包含所有列，列名，列值）
                Map<String ,Object> map = new HashMap();
                //遍历一行数据的所有列
                    //getColumnCount获取列数
                for (int i = 0; i <metaData.getColumnCount() ; i++) {
                    //根据下标获取列名
                    String key=metaData.getColumnName(i+1);
                    //根据下标获取列值
                    Object value = resultSet.getObject(i + 1);
                    map.put(key,value);
                }
                rowList.add(map);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            closeAll(resultSet,preparedStatement,connection);
        }
        return  rowList;
    }
    public static void main(String[] args) throws Exception {
        BaseDao  baseDao= new BaseDao();

        //3拼写sql语句
       // String sql="insert into tbl_teacher (birthday ,teacher_name) values (?,?)";
        // String sql = "update tbl_teacher set teacher_name=? where id=?";
       // baseDao.executeUpdate(sql,new Date(System.currentTimeMillis()),"郭青松");
       //baseDao.executeUpdate(sql,"郭靖",3);
       // String sql="delete from tbl_teacher where id=?";
      //  baseDao.executeUpdate(sql,3);

        String  sql = "select * from tbl_teacher";
        List<Map<String, Object>> mapList = baseDao.executeQuery(sql);
        for (Map<String, Object> stringObjectMap : mapList) {
            System.out.println(stringObjectMap);
        }


    }


}
